package org.cunamas.web.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;

public class ExcelUtil {
	
	 String fileName;
	 
	 String fileNameTemplate;
	 
	 HSSFWorkbook workBook;
	
	 HSSFSheet hssfSheet;
	
	 HSSFRow row;
	 
	 HSSFCellStyle styleGreenCenter = null;
	 HSSFCellStyle styleOrangeCenter = null;
	 HSSFCellStyle styleYellowCenter = null;
	 HSSFCellStyle styleRedCenter = null;
	
	public ExcelUtil(String fileNameTemplate) {
		try {
			this.fileNameTemplate=fileNameTemplate;
			FileInputStream fileInputStream = new FileInputStream(fileNameTemplate);
			POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
			workBook = new HSSFWorkbook(fsFileSystem);
			
			createStyles(workBook);
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	private void createStyles(HSSFWorkbook wb) {
		HSSFFont fontBlack = wb.createFont();
		fontBlack.setFontName("Verdana");
		fontBlack.setFontHeightInPoints((short)6);
		
		styleGreenCenter = wb.createCellStyle();
		styleGreenCenter.setFont(fontBlack);
		styleGreenCenter.setFillForegroundColor(HSSFColor.GREEN.index);
		styleGreenCenter.setFillPattern(CellStyle.SOLID_FOREGROUND);
		styleGreenCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		styleGreenCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		styleGreenCenter.setWrapText(true);
		styleGreenCenter.setLocked(true);
//		styleGreenCenter.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
//		styleGreenCenter.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
//		styleGreenCenter.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
//		styleGreenCenter.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
		
		styleOrangeCenter = wb.createCellStyle();
		styleOrangeCenter.setFont(fontBlack);
		styleOrangeCenter.setFillForegroundColor(HSSFColor.ORANGE.index);
		styleOrangeCenter.setFillPattern(CellStyle.SOLID_FOREGROUND);
		styleOrangeCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		styleOrangeCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		styleOrangeCenter.setWrapText(true);
		styleOrangeCenter.setLocked(true);
		
		styleYellowCenter = wb.createCellStyle();
		styleYellowCenter.setFont(fontBlack);
		styleYellowCenter.setFillForegroundColor(HSSFColor.YELLOW.index);
		styleYellowCenter.setFillPattern(CellStyle.SOLID_FOREGROUND);
		styleYellowCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		styleYellowCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		styleYellowCenter.setWrapText(true);
		styleYellowCenter.setLocked(true);
		
		styleRedCenter = wb.createCellStyle();
		styleRedCenter.setFont(fontBlack);
		styleRedCenter.setFillForegroundColor(HSSFColor.RED.index);
		styleRedCenter.setFillPattern(CellStyle.SOLID_FOREGROUND);
		styleRedCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		styleRedCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		styleRedCenter.setWrapText(true);
		styleRedCenter.setLocked(true);
	}
	
	public HSSFCellStyle retornaEstiloPorCalificacion(String calificacion){

		if(calificacion==null) return null;
		
		if(calificacion.equals(CunamasConstants.CALIFICACION_OPTIMO)){
			return styleGreenCenter;
		}
		
		if(calificacion.equals(CunamasConstants.CALIFICACION_BUENO)){
			return styleOrangeCenter;
		}
		
		if(calificacion.equals(CunamasConstants.CALIFICACION_REGULAR)){
			return styleYellowCenter;
		}
		
		if(calificacion.equals(CunamasConstants.CALIFICACION_DEFICIENTE)){
			return styleRedCenter;
		}
		
		return null;
	}

	public void setSheet(int index){
		hssfSheet = workBook.getSheetAt(index);
	}

	public void setRow(int rowIndex){
		row = hssfSheet.getRow(rowIndex);
	}
	
	public HSSFCell getCell(int cellnum){
		return row.getCell(cellnum);
	}
	
	public String  createExcel(String fileNameTemplate, String fileName)  {
    	
        try {
        	String copy = fileCopy(fileNameTemplate,fileName);
        	
        	if(copy.length()>0){
	        	FileOutputStream fileOut = new FileOutputStream(fileName); 
				workBook.write(fileOut);
				fileOut.close();
        	}
			
		} catch (IOException e) {
			e.printStackTrace();
			return "fail";
		} 
        
        return "ok";
	}
	
	public String fileCopy(String fileNameTemplate, String fileName) {

//		SimpleDateFormat formateador= new SimpleDateFormat("dd_MM_yyyy_HH_mm");
		String sourceFile = fileNameTemplate; //RUTA_TEMPLATE+CunamasConstants.CROSS_REPORT_TEMPLATE_NAME+".xls";
		String destinationFile = fileName; //fileName+formateador.format(new Date())+".xls";
		
		System.out.println("Desde: " + sourceFile);
		System.out.println("Hacia: " + destinationFile);

		try {
			File inFile = new File(sourceFile);
			File outFile = new File(destinationFile);

			FileInputStream in = new FileInputStream(inFile);
			FileOutputStream out = new FileOutputStream(outFile);

			int c;
			while( (c = in.read() ) != -1)
				out.write(c);

			in.close();
			out.close();
			return destinationFile;
		} catch(IOException e) {
			System.err.println("Hubo un error de entrada/salida!!!");
			return "";
		}
	}

}
